package com.zut.dao;

import com.zut.domain.Student;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface StudentDao {
    @Insert("insert into student (user_id,coach_id,user_password,user_name,user_sex," +
            "user_age,user_number,user_mobile,user_home,user_introduction,user_date)" +
            " values(#{userId},#{coachId},#{password},#{name},#{sex},#{age},#{idCard},#{mobile}," +
            "#{home},#{introduction},#{date})")
    int insert(Student student);

    @Select("select student.user_id as userId,coach_id as coachId,user_name as name,user_sex as sex," +
            "user_age as age, user_number as idCard,user_mobile as mobile,user_img as img,user_home as home," +
            "user_introduction as introduction,user_date as date,count(exam_grade >= 80) as subject " +
            "from student left join exam_result on student.user_id = exam_result.user_id " +
            "group by student.user_id")
    List<Map<String, String>> getAll();

    @Results(id = "groupWithStudent",
            value = {
                    @Result(property = "userId", column = "user_id", id = true),
                    @Result(property = "coachId", column = "coach_id"),
                    @Result(property = "name", column = "user_name"),
                    @Result(property = "sex", column = "user_sex"),
                    @Result(property = "age", column = "user_age"),
                    @Result(property = "idCard", column = "user_number"),
                    @Result(property = "mobile", column = "user_mobile"),
                    @Result(property = "img", column = "user_img"),
                    @Result(property = "home", column = "user_home"),
                    @Result(property = "introduction", column = "user_introduction"),
                    @Result(property = "date", column = "user_date")
            })
    @Select("select * from student where user_id=#{userId} and user_password=#{password}")
    Student getByNP(Student student);

    @Delete("delete from student where user_id = #{userId};")
    int deleteById(String userId);

    @ResultMap("groupWithStudent")
    @Select("select * from student where user_id = #{userId}")
    Student selectById(String userId);

    @Select("select student.user_id as userId,coach_id as coachId,user_name as name,user_sex as sex," +
            "user_age as age, user_number as idCard,user_mobile as mobile,user_img as img,user_home as home," +
            "user_introduction as introduction,user_date as date,count(exam_grade >= 80) as subject " +
            "from student left join exam_result on student.user_id = exam_result.user_id " +
            "where student.user_id = #{id} group by student.user_id;")
    Map<String, String> getById(String id);

    @Update("<script>                                                                        "
            + "update student                                                                "
            + "<set>                                                                         "
            + "  <if test='coachId != null'> coach_id=#{coachId},                       </if>"
            + "  <if test='password != null'> user_password=#{password},                </if>"
            + "  <if test='name != null'> user_name=#{name},                            </if>"
            + "  <if test='sex != null'> user_sex=#{sex},                               </if>"
            + "  <if test='age != null'> user_age=#{age},                               </if>"
            + "  <if test='idCard != null'> user_number=#{idCard},                      </if>"
            + "  <if test='mobile != null'> user_mobile=#{mobile},                      </if>"
            + "  <if test='img != null'> user_img=#{img},                               </if>"
            + "  <if test='home != null'> user_home=#{home},                            </if>"
            + "  <if test='introduction != null'> user_introduction=#{introduction},    </if>"
            + "  <if test='date != null'> user_date=#{date},                            </if>"
            + "</set>                                                                        "
            + "where user_id = #{userId}                                                     "
            + "</script>                                                                     ")
    int update(Student student);

    @Select("select coach_id from student where user_id = #{userId}")
    String getCoach(String userId);

    @Select("select user_password from student where user_id = #{userId}")
    String getPassword(String userId);

    @Select("select student.user_name as name," +
            "student.user_age as age," +
            "student.user_id as userId," +
            "student.user_mobile as mobile," +
            "student.user_date as date," +
            "student.user_img as img," +
            "exam.exam_place as examPlace," +
            "exam.exam_date as examDate," +
            "practise.interval_time as intervalTime," +
            "order_practise.finish_time as finishTime " +
            "from student " +
            "left join exam_result on exam_result.user_id = student.user_id and exam_result.exam_grade is null " +
            "left join exam on exam.exam_id = exam_result.exam_id " +
            "left join order_practise on order_practise.user_id = student.user_id " +
            "left join practise on practise.practise_id = order_practise.practise_id and order_practise.status = '1' " +
            "where student.coach_id = #{coachId}")
    List<Map<String,String>> getByCoach(String coachId);

    @ResultMap("groupWithStudent")
    @Select("select student.user_id,coach_id,user_name,user_sex,user_age,user_number," +
            "user_mobile,user_img,user_home,user_introduction,user_date from student," +
            "exam_result where student.user_id = exam_result.user_id group by  exam_result.user_id having avg(exam_grade) > 95;")
    List<Student> getBestStudent();

    @Update("<script>                                                                      "
            + "update student                                                                "
            + "<set>                                                                       "
            + "  <if test='params.newPassword != null'> user_password=#{params.newPassword},                         </if>"
            + "</set>                                                                      "
            + "where user_id = #{params.id}                                                      "
            + "</script>                                                                   ")
    int updatePwd(@Param("params") HashMap<String, String> map);


    @Select("select user_id as userId,coach_id as coachId,user_name as name ,user_sex as, sex from student ")
    List<Map<String, String>> getTest();


}